﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Configuration;
using System.Text.RegularExpressions;

namespace ExcelFileQueryTool
{
    public sealed class ExcelWorkLogService
    {
        private SimpleFileWatcher _watcher;
        private string _watcherSessionId = Guid.NewGuid().ToString();

        public ExcelWorkLogService(String file)
        {
            if (string.IsNullOrEmpty(file))
                throw new ArgumentNullException("file");

            if (!File.Exists(file))
                throw new FileNotFoundException("file not found", file);

            ExcelFileName = file;
            _watcher = new SimpleFileWatcher(file, _watcherSessionId);
        }

        private static readonly IExcelWorkLogFilter Filter = new SpecialKeywordExcelWorkLogFilter();
        public IExcelWorkLogFilter WorkLogFilter { get { return Filter; } }

        // 浅绿： R146, G208, B80
        private static readonly Color RecordedOnBookColor_System = Color.FromArgb(146, 208, 80);

        // 客户供应商列的索引位置
        private const int COL_INDEX_CLIENT_PROVIDER = 3;

        public String ExcelFileName { get; private set; }

        public bool FileHasChanged { get { return _watcher.FileHasChanged; } }

        public String ClientFileFolder { get { return ConfigurationManager.AppSettings["ClientFileFolder"]; } }

        private IList<ExcelWorkLogSheetInfo> _sheetInfos;

        public IEnumerable<ExcelWorkLogSheetInfo> SheetInfos
        {
            get
            {
                if (_sheetInfos == null)
                {
                    using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
                    {
                        var book = package.Workbook;

                        if (book.Worksheets.Count < 2)
                            throw new Exception("读取失败，无效的日记文件");

                        _sheetInfos = new List<ExcelWorkLogSheetInfo>(book.Worksheets.Count - 2);

                        const int ROW_BEGIN = 5;

                        foreach (var sheet in book.Worksheets)
                        {
                            if (sheet.IsDataSheet() == false)
                                continue;

                            if (sheet.Dimension == null)
                                continue;

                            var sheetInfo = new ExcelWorkLogSheetInfo(sheet.Name,
                                                                ROW_BEGIN,
                                                                sheet.Dimension.Rows,
                                                                sheet.TabColor == RecordedOnBookColor_System,
                                                                !string.IsNullOrEmpty(sheet.Cells[ROW_BEGIN, COL_INDEX_CLIENT_PROVIDER].GetValue<String>()));

                            _sheetInfos.Add(sheetInfo);
                        }
                    }
                }

                return _sheetInfos;
            }
        }

        private IList<ExcelWorkLogItem> _workLogItems;

        public IEnumerable<ExcelWorkLogItem> QueryWorkLogItems(IEnumerable<string> sheets, string keyword)
        {
            if (sheets == null)
                return null;

            if (_workLogItems == null)
                _workLogItems = new List<ExcelWorkLogItem>(200);


            bool modified = false;

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
            {
                var book = package.Workbook;

                foreach (var sheetName in sheets)
                {
                    var sheetInfo = SheetInfos.FirstOrDefault(x => x.Name == sheetName);

                    if (sheetInfo == null)
                        continue;

                    if (sheetInfo.HasContentRowLoaded == false)
                    {
                        LoadWorkSheet(_workLogItems, book, sheetInfo, out modified);
                        sheetInfo.HasContentRowLoaded = true;

                        // 如果载入数据的过程中有对数据进行修正，则modified的值为true，需要进行保存
                        if (modified)
                            package.Save();
                    }
                }
            }

            if (modified)
                _watcher.UpdateModifiedTime(_watcherSessionId);

            return Filter.Apply(_workLogItems.Where(item => sheets.Contains(item.SheetName)), keyword);
        }

        private static void LoadWorkSheet(IList<ExcelWorkLogItem> items, ExcelWorkbook book, ExcelWorkLogSheetInfo sheetInfo, out bool modified)
        {
            modified = false;
            const decimal INVALID_CONCRETE_PURCHASE_AMOUNT = -99m;
            const string VALIDATION_TARGET_ITEM = "水泥";
            var sheet = book.Worksheets[sheetInfo.Name];
            int lastRowIndex = sheetInfo.DataRowEnd;
            decimal currentConcretePurchaseAmountLeft = INVALID_CONCRETE_PURCHASE_AMOUNT;
            Regex regex_Note_Amount = new Regex(AppConfig.WorkLogFileNoteAmountRegexPattern);
            string[] shipping_no_validation = { "三轮", "摩托", "手拖", "的士头" };

            // row 1/2/3 is conclusion data,row 4 is header
            for (int rowIdx = sheetInfo.DataRowBegin; rowIdx <= lastRowIndex; rowIdx++)
            {
                // 客户供应商名称
                var workTarget = sheet.Cells[rowIdx, COL_INDEX_CLIENT_PROVIDER].GetValue<String>();
                if (String.IsNullOrEmpty(workTarget))
                    continue;
                //throw new Exception(string.Format("读取日记失败，缺少客户/供应商名称(sheet={0},row={1})", sheet.Name, rowIdx));

                if (workTarget.StartsWith(" ") || workTarget.EndsWith(" "))
                {
                    modified = true;
                    workTarget = workTarget.Trim();
                    sheet.Cells[rowIdx, COL_INDEX_CLIENT_PROVIDER].Value = workTarget;
                }

                // Column A to M,count to 13
                var workLogItem = new ExcelWorkLogItem();
                // 这里只需获取必要的字段用于验证，待验证通过后再获取其它的值
                workLogItem.类型 = sheet.Cells[rowIdx, 2].GetValue<String>() ?? string.Empty;
                workLogItem.货物 = sheet.Cells[rowIdx, 4].GetValue<String>() ?? string.Empty;
                workLogItem.数量 = sheet.Cells[rowIdx, 6].GetValue<Decimal>();
                workLogItem.运输方式 = sheet.Cells[rowIdx, 10].GetValue<String>() ?? string.Empty;
                workLogItem.备注 = sheet.Cells[rowIdx, 11].GetValue<String>() ?? string.Empty;

                #region 验证水泥进货/销售是否对称

                // 水泥需要验证进货数量和销售/进仓数量，其它材料跳过验证
                // 水泥需要对进货/进仓/销售进行项目名称合并操作，其它材料则不需要合并
                // 水泥的出货方式有 档口/销售/过车
                if (workLogItem.运输方式 != null && shipping_no_validation.All(x => workLogItem.运输方式.Contains(x) == false))
                {
                    switch (workLogItem.类型)
                    {
                        case "进货":
                            //todo:同一部车连续进货应合并
                            if (currentConcretePurchaseAmountLeft > 0m)
                                throw new Exception(string.Format("读取日志失败，前一次水泥进货记录未卸车完毕，记录不完整(post:sheet={0},row={1})", sheet.Name, rowIdx - 1));

                            if (workLogItem.货物.EndsWith(VALIDATION_TARGET_ITEM))
                            {
                                currentConcretePurchaseAmountLeft = workLogItem.数量;
                                if (string.IsNullOrEmpty(workLogItem.备注) == false)
                                {
                                    var matches = regex_Note_Amount.Matches(workLogItem.备注);
                                    foreach (Match m in matches)
                                    {
                                        decimal amount = Convert.ToDecimal(m.Groups[1].Value);
                                        currentConcretePurchaseAmountLeft -= amount;
                                    }
                                }
                            }
                            break;
                        case "销售":
                            if (currentConcretePurchaseAmountLeft > 0m)
                            {
                                if (workLogItem.货物.Contains(VALIDATION_TARGET_ITEM))
                                    currentConcretePurchaseAmountLeft -= workLogItem.数量;
                                else
                                    throw new Exception(string.Format("post:读取日志失败，前一次水泥进货记录未卸车完毕，记录不完整(sheet={0},row={1})", sheet.Name, rowIdx - 1));
                            }
                            else
                            {
                                // do nothing
                            }
                            break;

                        default:
                            break;
                    }

                    // 上面的报错都是针对上一次的进货/销售记录进行验证，如果这次进货/销售记录是最后一组记录，那么需要在这里进行验证
                    if (rowIdx == lastRowIndex && currentConcretePurchaseAmountLeft > 0m)
                        throw new Exception(string.Format("current:读取日志失败，本次水泥进货记录未卸车完毕，记录不完整(sheet={0},row={1})", sheet.Name, rowIdx));

                    // reset amount to make next validation works
                    if (currentConcretePurchaseAmountLeft != INVALID_CONCRETE_PURCHASE_AMOUNT && currentConcretePurchaseAmountLeft <= 0m)
                    {
                        currentConcretePurchaseAmountLeft = INVALID_CONCRETE_PURCHASE_AMOUNT;
                    }
                }

                #endregion

                #region Parse values

                workLogItem.SheetName = sheetInfo.Name ?? string.Empty;
                workLogItem.RowIndex = rowIdx;
                workLogItem.HasRecordOnBook = RecordedOnBookColor_System.Name.Equals(sheet.Cells[rowIdx, COL_INDEX_CLIENT_PROVIDER].Style.Fill.BackgroundColor.Rgb, StringComparison.OrdinalIgnoreCase);

                workLogItem.日期 = sheet.Cells[rowIdx, 1].GetValue<DateTime>();
                workLogItem.客户供应商 = workTarget;
                workLogItem.单位 = sheet.Cells[rowIdx, 5].GetValue<String>() ?? string.Empty;
                workLogItem.单价 = sheet.Cells[rowIdx, 7].GetValue<Decimal>();
                workLogItem.运装卸费 = sheet.Cells[rowIdx, 8].GetValue<Decimal>();
                // cell 9 will be automically computed                
                workLogItem.已收付货款 = sheet.Cells[rowIdx, 12].GetValue<Decimal>();
                workLogItem.收付款人 = sheet.Cells[rowIdx, 13].GetValue<String>() ?? string.Empty;

                #endregion

                items.Add(workLogItem);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="items"></param>
        public void MarkAsRecordedOnBook(IEnumerable<ExcelWorkLogItem> items)
        {
            if (items == null || items.Count() == 0)
                throw new ArgumentNullException("items");

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
            {
                var book = package.Workbook;

                foreach (var group in items.GroupBy(item => item.SheetName))
                {
                    var sheet = book.Worksheets[group.Key];
                    if (sheet == null)
                        continue;

                    foreach (var log in group)
                    {
                        SetRecordedStatusAndStyle(sheet, log);
                    }

                    if (_workLogItems.Where(item => item.SheetName == group.Key).All(item => item.HasRecordOnBook))
                        sheet.TabColor = RecordedOnBookColor_System;
                }

                package.Save();
            }

            _watcher.UpdateModifiedTime(_watcherSessionId);
        }

        private static void SetRecordedStatusAndStyle(ExcelWorksheet sheet, ExcelWorkLogItem log)
        {
            log.HasRecordOnBook = true;

            sheet.Cells[log.RowIndex, COL_INDEX_CLIENT_PROVIDER].Style.Fill.PatternType = ExcelFillStyle.Solid;
            sheet.Cells[log.RowIndex, COL_INDEX_CLIENT_PROVIDER].Style.Fill.BackgroundColor.SetColor(RecordedOnBookColor_System);
        }

        public void Save(ExcelWorkLogItem log)
        {
            if (log == null)
                throw new ArgumentNullException();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
            {
                var sheet = package.Workbook.Worksheets[log.SheetName];
                if (sheet == null)
                    throw new Exception("保存失败，日志文件中不存在名称为[" + log.SheetName + "]的表！");

                FillValues(sheet, log);

                package.Save();
            }

            _watcher.UpdateModifiedTime(_watcherSessionId);
        }

        private static void FillValues(ExcelWorksheet sheet, ExcelWorkLogItem log)
        {
            var rowIdx = log.RowIndex;

            sheet.Cells[rowIdx, 1].Value = log.日期;
            sheet.Cells[rowIdx, 2].Value = log.类型;
            sheet.Cells[rowIdx, COL_INDEX_CLIENT_PROVIDER].Value = log.客户供应商;
            sheet.Cells[rowIdx, 4].Value = log.货物;
            sheet.Cells[rowIdx, 5].Value = log.单位;
            sheet.Cells[rowIdx, 6].Value = log.数量;
            sheet.Cells[rowIdx, 7].Value = log.单价;
            sheet.Cells[rowIdx, 8].Value = log.运装卸费;
            // cell 9 will be automically computed
            sheet.Cells[rowIdx, 10].Value = log.运输方式;
            sheet.Cells[rowIdx, 11].Value = log.备注;
            sheet.Cells[rowIdx, 12].Value = log.已收付货款;
            sheet.Cells[rowIdx, 13].Value = log.收付款人;
        }

        public void Save(IEnumerable<ExcelWorkLogItem> logList)
        {
            if (logList == null || logList.Count() == 0)
                throw new ArgumentNullException();

            var sheetGroups = logList.GroupBy(x => x.SheetName);

            using (ExcelPackage package = new ExcelPackage(new FileInfo(ExcelFileName)))
            {
                foreach (var group in sheetGroups)
                {
                    foreach (var log in group)
                    {
                        var sheet = package.Workbook.Worksheets[group.Key];
                        if (sheet == null)
                            throw new Exception("保存失败，日志文件中不存在名称为[" + log.SheetName + "]的表！");

                        FillValues(sheet, log);

                        if (log.IsPaidOff && AppConfig.DirectPayRecivers.Contains(log.收付款人))
                            SetRecordedStatusAndStyle(sheet, log);
                    }
                }

                package.Save();
            }

            _watcher.UpdateModifiedTime(_watcherSessionId);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="item"></param>
        public void MarkAsRecordedOnBook(ExcelWorkLogItem item)
        {
            if (item == null)
                throw new ArgumentNullException("item");

            MarkAsRecordedOnBook(new ExcelWorkLogItem[] { item });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        //public IEnumerable<RecordOnBookResult> PrepareClientFiles(IEnumerable<ExcelWorkLogItem> items)
        //{
        //    SortableBindingList<RecordOnBookResult> results = new SortableBindingList<RecordOnBookResult>();

        //    foreach (var group in items.GroupBy(item => item.客户供应商))
        //    {
        //        var r = new RecordOnBookResult();

        //        r.ClientProviderName = group.Key;
        //        r.WorkLogItems = group;

        //        var files = Directory.GetFiles(AppSettings.ClientFileFolder, String.Format("[对数表]*{0}*.xlsx", r.ClientProviderName), SearchOption.AllDirectories);

        //        if (files != null && files.Length > 0)
        //        {
        //            var filesInfo = new List<ClientFileInfo>();

        //            foreach (var f in files)
        //            {
        //                using (ExcelPackage package = new ExcelPackage(new FileInfo(f)))
        //                {
        //                    var sheetNames = new List<String>();

        //                    foreach (var sheet in package.Workbook.Worksheets)
        //                    {
        //                        sheetNames.Add(sheet.Name);
        //                    }

        //                    var info = new ClientFileInfo { FileName = f };
        //                    info.SheetNames = sheetNames;
        //                    filesInfo.Add(info);
        //                }
        //            }

        //            r.MatchedFiles = filesInfo;
        //        }

        //        results.Add(r);
        //    }

        //    return results;
        //}
    }
}
